﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Remoting.Contexts;
using System.Text;
using System.Threading.Tasks;
using System.Xml.Linq;
using MySql.Data.MySqlClient;

namespace DSYSaveMDBData
{
    public class MySQL
    {
        public MySqlCommand comm = null;//语句执行对象
        public MySqlDataReader dr = null;//语句执行结果数据对象
        MySqlConnectionStringBuilder builder = null;

        public MySQL(string DatebaseName, string Server, uint Port, string Password, string UserID, string TableName)
        {
            this.DatebaseName = DatebaseName;
            this.Server = Server;
            this.Port = Port;
            this.Password = Password;
            this.UserID = UserID;
            this.TableName = $"{TableName}";
            Datebase_Table = $"{DatebaseName}.`{TableName}`";
            if (!CheckDatabaseExists(DatebaseName))
                load(true);
            load();
        }

        //用于一个窗口让用户自定义输入用户名与密码，在这里我直接定义好了
        string DatebaseName { get; set; }
        string Server { get; set; }
        uint Port { get; set; }
        string Password { get; set; }
        string UserID { get; set; }
        string TableName { get; set; }
        string Datebase_Table { get; set; }
        string charset { get; set; } = " utf8";

        public void load(bool IsCreateDatabase = false)
        {
            //直接写链接语句，比较容易出错
            //conn = new MySqlConnection(
            //    "Database = data;Server = localhost;Port = 3306;Password = " + pwd + ";UserID = " + uid + ";charset = utf8mb4");
            //conn.Open();

            //使用Builder写语句，实现分段
            //与数据库连接的信息
            builder = new MySqlConnectionStringBuilder();
            //数据库连接时的用户名，可以用pid
            builder.UserID = UserID;
            builder.Port = Port;
            //数据库连接时的密码，可以用pwd
            builder.Password = Password;
            //数据库连接时的服务器地址
            builder.Server = Server;
            builder.CharacterSet = charset;
            
            if (IsCreateDatabase)
            {
                using (MySqlConnection conn = new MySqlConnection(builder.ConnectionString))
                {
                    comm = new MySqlCommand(string.Format("CREATE DATABASE {0}", DatebaseName), conn);
                    conn.Open();
                    comm.ExecuteNonQuery();
                    conn.Close();
                }
            }
            else
            {
                //要连接的数据库
                builder.Database = DatebaseName;
            }
        }

        public List<string> Read()
        {
            List<string> list = new List<string>();
            using (MySqlConnection conn = new MySqlConnection(builder.ConnectionString))
            {
                try
                {
                    conn.Open();
                    //查询语句
                    string sql = string.Format("select * from {0} ", Datebase_Table);
                    comm = new MySqlCommand(sql, conn);
                    dr = comm.ExecuteReader();

                    while (dr.Read())
                    {
                        string str = string.Empty;
                        for (int i = 0; i < dr.FieldCount; i++)
                        {
                            str += dr.GetString(i) + ",";
                        }
                        list.Add(str.Substring(0, str.Length - 1));
                    }
                }
                catch { }
                conn.Close();
            }
            return list;
        }

        public List<string> ReadColumnName()
        {
            List<string> list = new List<string>();
            using (MySqlConnection conn = new MySqlConnection(builder.ConnectionString))
            {
                try
                {
                    conn.Open();
                    //查询语句
                    string sql = string.Format("SHOW COLUMNS FROM {0}", TableName);
                    comm = new MySqlCommand(sql, conn);
                    dr = comm.ExecuteReader();
                    while (dr.Read())
                    {
                        list.Add((string)dr.GetString(0));
                    }
                }
                catch { }
                conn.Close();
            }
            return list;
        }

        public List<string> Read(string columnName, string value)
        {
            List<string> list = new List<string>();
            using (MySqlConnection conn = new MySqlConnection(builder.ConnectionString))
            {
                try
                {
                    conn.Open();
                    //查询语句
                    string sql = string.Format("select * from {0} where `{1}`={2}", Datebase_Table, columnName, value);
                    comm = new MySqlCommand(sql, conn);
                    dr = comm.ExecuteReader();
                    while (dr.Read())
                    {
                        for (int i = 0; i < dr.FieldCount; i++)
                        {
                            list.Add(dr.GetString(i));
                        }
                        //string str = dr["barcode"].ToString();
                        //string str1 = dr["new_tablecol"].ToString();
                    }
                }
                catch { }
                conn.Close();
            }
            return list;
        }

        public void Add(List<string> name, List<string> value)
        {
            List<string> strings = ReadColumnName();
            foreach (string s in name)
            {
                if (strings.FindAll(x => x == s).Count == 0)
                    AddColumn(s, "VARCHAR(45)");
            }
            using (MySqlConnection conn = new MySqlConnection(builder.ConnectionString))
            {
                try
                {
                    conn.Open();
                    string str = string.Empty;
                    string str1 = string.Empty;
                    for (int i = 0; i < name.Count; i++)
                    {
                        str1 += string.Format("`{0}`,", name[i]);
                        if (value[i].Contains("Na"))
                            str += string.Format("{0},", "9999");
                        else
                            str += string.Format("{0},", value[i]);
                    }
                    string sql = string.Format("INSERT INTO {0} ({1}) VALUES ({2})", Datebase_Table, str1.Substring(0, str1.Length - 1), str.Substring(0, str.Length - 1));
                    comm = new MySqlCommand(sql, conn);
                    comm.ExecuteNonQuery();
                }
                catch { }
                conn.Close();
            }
        }

        public void Update(List<string> strName, List<string> strValue, string columnName, string value)
        {
            using (MySqlConnection conn = new MySqlConnection(builder.ConnectionString))
            {
                try
                {
                    conn.Open();
                    string str = string.Empty;
                    for (int i = 0; i < strValue.Count; i++)
                    {
                        if (strValue[i].Contains("Na"))
                            str += $"`{strName[i]}` = 9999,";
                        else
                            str += $"`{strName[i]}` ={strValue[i]},";
                    }
                    string sql = string.Format("UPDATE {0} SET {1} where {2}={3}", Datebase_Table, str.Substring(0, str.Length - 1), columnName, value);
                    comm = new MySqlCommand(sql, conn);
                    comm.ExecuteNonQuery();
                }
                catch { }
                conn.Close();
            }
        }

        public void Delet(string columnName, string value)
        {
            using (MySqlConnection conn = new MySqlConnection(builder.ConnectionString))
            {
                try
                {
                    conn.Open();
                    string sql = string.Format("delete from {0}  where `{1}`={2}", Datebase_Table, columnName, value);
                    comm = new MySqlCommand(sql, conn);
                    comm.ExecuteNonQuery();
                }
                catch { }
                conn.Close();
            }
        }

        public void AddColumn(string columnName, string type)
        {
            using (MySqlConnection conn = new MySqlConnection(builder.ConnectionString))
            {
                try
                {
                    conn.Open();
                    string sql = string.Format("ALTER TABLE {0}  ADD `{1}` {2}", TableName, columnName, type);
                    comm = new MySqlCommand(sql, conn);
                    comm.ExecuteNonQuery();
                }
                catch { }
                conn.Close();
            }
        }

        public void CreateTable(List<string> columnName, List<string> type)
        {
            using (MySqlConnection conn = new MySqlConnection(builder.ConnectionString))
            {
                try
                {
                    conn.Open();
                    string str = string.Empty;
                    for (int i = 0; i < columnName.Count; i++)
                    {
                        if (i == 0)
                            str += $"`{columnName[i]}` {type[i]} NOT NULL,";
                        else
                            str += $"`{columnName[i]}` {type[i]} NULL,";
                    }
                    string sql = $"CREATE TABLE `{DatebaseName}`.`{TableName}`({str}PRIMARY KEY(`{columnName[0]}`))";

                    comm = new MySqlCommand(sql, conn);
                    comm.ExecuteNonQuery();
                    conn.Close();
                }
                catch { }
            }
        }

        public void AddBiteLength(string columnName, string type)
        {
            using (MySqlConnection conn = new MySqlConnection(builder.ConnectionString))
            {
                try
                {
                    conn.Open();
                    string sql = string.Format("ALTER TABLE {0} MODIFY `{1}` {2}", Datebase_Table, columnName, type);
                    comm = new MySqlCommand(sql, conn);
                    comm.ExecuteNonQuery();
                }
                catch { }
                conn.Close();
            }
        }

        public int GetRowConut()
        {
            int Row = 0;
            using (MySqlConnection conn = new MySqlConnection(builder.ConnectionString))
            {
                try
                {
                    conn.Open();
                    string sql = string.Format("select count(*) from {0}", Datebase_Table);
                    comm = new MySqlCommand(sql, conn);
                    Row = Convert.ToInt32(comm.ExecuteScalar());
                }
                catch { }
                conn.Close();
            }
            return Row;
        }

        public bool IsExiteData()
        {
            bool tableExists = false;
            using (MySqlConnection conn = new MySqlConnection(builder.ConnectionString))
            {
                try
                {
                    conn.Open();
                    string sql = string.Format("select TABLE_NAME from information_schema.TABLES WHERE TABLE_SCHEMA='{0}' AND TABLE_NAME = '{1}'", DatebaseName, TableName);
                    comm = new MySqlCommand(sql, conn);
                    tableExists = comm.ExecuteReader().HasRows;
                }
                catch { }
                conn.Close();
            }
            return tableExists;
        }

        public bool CheckDatabaseExists(string databaseName)
        {
            using (var connection = new MySqlConnection("server=localhost;user=root;password=123456"))
            {
                connection.Open();

                // 查询信息架构以获取所有数据库名称
                var cmd = new MySqlCommand("SHOW DATABASES", connection);
                var reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    string str = reader.GetString(0);
                    if (reader.GetString(0) == databaseName)
                    {
                        // 数据库存在
                        return true;
                    }
                }

                // 数据库不存在
                return false;
            }
        }
    }
}
